Skip Headers

Oracle® Streams Replication Administrator's Guide 10g Release 2 (10.2) Part Number B14228-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 13 Troubleshooting Streams Replication

This chapter contains information about identifying and resolving common problems in a Streams replication environment.

This chapter contains these topics:

- Recovering from Configuration Errors

- Troubleshooting an Apply Process in a Replication Environment

See Also: Oracle Streams Concepts and Administration for more information about troubleshooting Streams environments

## Recovering from Configuration Errors

The following procedures in the `DBMS_STREAMS_ADM` package configure a replication environment that is maintained by Streams:

- `MAINTAIN_GLOBAL`

- `MAINTAIN_SCHEMAS`

- `MAINTAIN_SIMPLE_TTS`

- `MAINTAIN_TABLES`

- `MAINTAIN_TTS`

- `PRE_INSTANTIATION_SETUP` and `POST_INSTANTIATION_SETUP`

When one of these procedures configures the replication environment directly (with the `perform_actions` parameter is set to `true`), information about the configuration actions is stored in the following data dictionary views when the procedure is running:

- `DBA_RECOVERABLE_SCRIPT`

- `DBA_RECOVERABLE_SCRIPT_PARAMS`

- `DBA_RECOVERABLE_SCRIPT_BLOCKS`

- `DBA_RECOVERABLE_SCRIPT_ERRORS`

When the procedure completes successfully, metadata about the configuration operation is purged from these views. However, when one of these procedures encounters an error and stops, metadata about the configuration operation remains in these views. Typically, these procedures encounter errors when one or more prerequisites for running them is not met.

When one of these procedures encounters an error, you can use the `RECOVER_OPERATION` procedure in the `DBMS_STREAMS_ADM` package to either roll the operation forward, roll the operation back, or purge the metadata about the operation. Specifically, the `operation_mode` parameter in the `RECOVER_OPERATION` procedure provides the following options:

- `FORWARD`: This option attempts to complete the configuration operation from the point at which it failed. Before specifying this option, correct the conditions that caused the errors reported in the `DBA_RECOVERABLE_SCRIPT_ERRORS` view.

- `ROLLBACK`: This option rolls back all of the actions performed by the configuration procedure. If the rollback is successful, then this options also purges the metadata about the operation in the data dictionary views described previously.

- `PURGE`: This option purges the metadata about the operation in the data dictionary views described previously without rolling the operation back.

Note: If the `perform_actions` parameter is set to `false` when one of the configuration procedures is run, and a script is used to configure the Streams replication environment, then the data dictionary views are not populated, and the `RECOVER_OPERATION` procedure cannot be used for the operation. To run the `RECOVER_OPERATION` procedure, both databases must be Oracle Database 10g Release 2 databases.

See Also: "Configuring Replication Using the DBMS_STREAMS_ADM Package" for more information about configuring a Streams replication environment with these procedures "Tasks to Complete Before Configuring Streams Replication" for information about prerequisites that must be met before running these procedures

### Recovery Scenario

This section contains a scenario in which the `MAINTAIN_SCHEMAS` procedure stops because it encounters an error. Assume that the following procedure encountered an error when it was run at the capture database:

    BEGIN
      DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
        schema_names                 => 'hr',
        source_directory_object      => 'SOURCE_DIRECTORY',
        destination_directory_object => 'DEST_DIRECTORY',
        source_database              => 'inst1.net',
        destination_database         => 'inst2.net',
        perform_actions              => true,
        dump_file_name               => 'export_hr.dmp',
        capture_queue_table          => 'rep_capture_queue_table',
        capture_queue_name           => 'rep_capture_queue',
        capture_queue_user           => NULL,
        apply_queue_table            => 'rep_dest_queue_table',
        apply_queue_name             => 'rep_dest_queue',
        apply_queue_user             => NULL,
        capture_name                 => 'capture_hr',
        propagation_name             => 'prop_hr',
        apply_name                   => 'apply_hr',
        log_file                     => 'export_hr.clg',
        bi_directional               => false,
        include_ddl                  => true,
        instantiation                => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);
    END;
    /

Complete the following steps to diagnose the problem and recover the operation:

1. Query the `DBA_RECOVERABLE_SCRIPT_ERRORS` data dictionary view at the capture database to determine the error:

       COLUMN SCRIPT_ID     HEADING 'Script ID'     FORMAT A35
       COLUMN BLOCK_NUM     HEADING 'Block|Number' FORMAT 999999
       COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A33

       SELECT SCRIPT_ID, BLOCK_NUM, ERROR_MESSAGE FROM DBA_RECOVERABLE_SCRIPT_ERRORS;

   The query returns the following output:

       Block
       Script ID                            Number Error Message
       ----------------------------------- ------- ---------------------------------
       F73ED2C9E96B27B0E030578CB10B2424         12 ORA-39001: invalid argument value

2. Query the `DBA_RECOVERABLE_SCRIPT_BLOCKS` data dictionary view for the script ID and block number returned in Step 1 for information about the block in which the error occurred. For example, if the script ID is `F73ED2C9E96B27B0E030578CB10B2424` and the block number is `12`, run the following query:

       COLUMN FORWARD_BLOCK        HEADING 'Forward Block'               FORMAT A50
       COLUMN FORWARD_BLOCK_DBLINK HEADING 'Forward Block|Database Link' FORMAT A13
       COLUMN STATUS               HEADING 'Status'                      FORMAT A12

       SET LONG 10000
       SELECT FORWARD_BLOCK,
              FORWARD_BLOCK_DBLINK,
              STATUS
         FROM DBA_RECOVERABLE_SCRIPT_BLOCKS
         WHERE SCRIPT_ID = 'F73ED2C9E96B27B0E030578CB10B2424' AND
               BLOCK_NUM = 12;

   The output contains the following information:

   - The `FORWARD_BLOCK` column contains detailed information about the actions performed by the procedure in the specified block. If necessary, spool the output into a file. In this scenario, the `FORWARD_BLOCK` column for block `12` contains the code for the Data Pump export.

   - The `FORWARD_BLOCK_DBLINK` column shows the database where the block is executed. In this scenario, the `FORWARD_BLOCK_DBLINK` column for block `12` shows `INST1.NET` because the Data Pump export was being performed on `INST1.NET` when the error occurred.

   - The `STATUS` column shows the status of the block execution. In this scenario, the `STATUS` column for block `12` shows `ERROR`.

3. Interpret the output of the queries and diagnose the problem. The output returned in Step 1 provides the following information:

   - The unique identifier for the configuration operation is `F73ED2C9E96B27B0E030578CB10B2424`. This value is the `RAW` value returned in the `SCRIPT_ID` field.

   - Only one Streams configuration procedure is in the process of running because only one row was returned by the query. If more than one row was returned by the query, then query the `DBA_RECOVERABLE_SCRIPT` and `DBA_RECOVERABLE_SCRIPT_PARAMS` views to determine which script ID applies to the configuration operation.

   - The cause in Oracle Database Error Messages for the `ORA-39001` error is the following: The user specified API parameters were of the wrong type or value range. Subsequent messages supplied by `DBMS_DATAPUMP.GET_STATUS` will further describe the error.

   - The query on the `DBA_RECOVERABLE_SCRIPT_BLOCKS` view shows that the error occurred during Data Pump export.

   The output from the queries shows that the `MAINTAIN_SCHEMAS` procedure encountered a Data Pump error. Notice that the `instantiation` parameter in the `MAINTAIN_SCHEMAS` procedure was set to `DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA`. This setting means that the `MAINTAIN_SCHEMAS` procedure performs the instantiation using a Data Pump export and import. A Data Pump export dump file is generated to complete the export/import.

   Data Pump errors usually are caused by one of the following conditions:

   - One or more of the directory objects used to store the export dump file do not exist.

   - The user running the procedure does not have access to specified directory objects.

   - An export dump file with the same name as the one generated by the procedure already exists in a directory specified in the `source_directory_object` or `destination_directory_object` parameter.

4. Query the `DBA_RECOVERABLE_SCRIPT_PARAMS` data dictionary view at the capture database to determine the names of the directory objects specified when the `MAINTAIN_SCHEMAS` procedure was run:

       COLUMN PARAMETER HEADING 'Parameter' FORMAT A30
       COLUMN VALUE     HEADING 'Value'     FORMAT A45

       SELECT PARAMETER,
              VALUE
              FROM DBA_RECOVERABLE_SCRIPT_PARAMS
              WHERE SCRIPT_ID = 'F73ED2C9E96B27B0E030578CB10B2424';

   The query returns the following output:

       Parameter                      Value
       ------------------------------ ---------------------------------------------
       SOURCE_DIRECTORY_OBJECT        SOURCE_DIRECTORY
       DESTINATION_DIRECTORY_OBJECT   DEST_DIRECTORY
       SOURCE_DATABASE                INST1.NET
       DESTINATION_DATABASE           INST2.NET
       CAPTURE_QUEUE_TABLE            REP_CAPTURE_QUEUE_TABLE
       CAPTURE_QUEUE_OWNER            STRMADMIN
       CAPTURE_QUEUE_NAME             REP_CAPTURE_QUEUE
       CAPTURE_QUEUE_USER
       APPLY_QUEUE_TABLE              REP_DEST_QUEUE_TABLE
       APPLY_QUEUE_OWNER              STRMADMIN
       APPLY_QUEUE_NAME               REP_DEST_QUEUE
       APPLY_QUEUE_USER
       CAPTURE_NAME                   CAPTURE_HR
       APPLY_NAME                     APPLY_HR
       PROPAGATION_NAME               PROP_HR
       INSTANTIATION                  INSTANTIATION_SCHEMA
       BI_DIRECTIONAL                 TRUE
       INCLUDE_DDL                    TRUE
       LOG_FILE                       export_hr.clg
       DUMP_FILE_NAME                 export_hr.dmp
       SCHEMA_NAMES                   HR

5. Make sure the directory object specified for the `source_directory_object` parameter exists at the source database, and make sure the directory object specified for the `destination_directory_object` parameter exists at the destination database. Check for these directory objects by querying the `DBA_DIRECTORIES` data dictionary view.

   For this scenario, assume that the `SOURCE_DIRECTORY` directory object does not exist at the source database, and the `DEST_DIRECTORY` directory object does not exist at the destination database. The Data Pump error occurred because the directory objects used for the export dump file did not exist.

6. Create the required directory objects at the source and destination databases using the SQL statement `CREATE` `DIRECTORY`. See "Create the Required Directory Objects" for instructions.

7. Run the `RECOVER_OPERATION` procedure at the capture database:

       BEGIN
         DBMS_STREAMS_ADM.RECOVER_OPERATION(
           script_id       => 'F73ED2C9E96B27B0E030578CB10B2424',
           operation_mode  => 'FORWARD');
       END;
       /

   Notice that the `script_id` parameter is set to the value determined in Step 1, and the `operation_mode` parameter is set to `FORWARD` to complete the configuration. Also, the `RECOVER_OPERATION` procedure must be run at the database where the configuration procedure was run.

## Troubleshooting an Apply Process in a Replication Environment

The following sections provide information about troubleshooting apply process problems in a replication environment:

- Is the Apply Process Encountering Contention?

- Is the Apply Process Waiting for a Dependent Transaction?

- Is an Apply Server Performing Poorly for Certain Transactions?

- Are There Any Apply Errors in the Error Queue?

### Is the Apply Process Encountering Contention?

An apply server is a component of an apply process. Apply servers apply DML and DDL changes to database objects at a destination database. An apply process can use one or more apply servers, and the `parallelism` apply process parameter specifies the number of apply servers that can concurrently apply transactions. For example, if `parallelism` is set to `5`, then an apply process uses a total of five apply servers.

An apply server encounters contention when the apply server must wait for a resource that is being used by another session. Contention can result from logical dependencies. For example, when an apply server tries to apply a change to a row that a user has locked, then the apply server must wait for the user. Contention can also result from physical dependencies. For example, interested transaction list (ITL) contention results when two transactions that are being applied, which might not be logically dependent, are trying to lock the same block on disk. In this case, one apply server locks rows in the block, and the other apply server must wait for access to the block, even though the second apply server is trying to lock different rows. See "Is the Apply Process Waiting for a Dependent Transaction?" for detailed information about ITL contention.

When an apply server encounters contention that does not involve another apply server in the same apply process, it waits until the contention clears. When an apply server encounters contention that involves another apply server in the same apply process, one of the two apply servers is rolled back. An apply process that is using multiple apply servers might be applying multiple transactions at the same time. The apply process tracks the state of the apply server that is applying the transaction with the lowest commit SCN. If there is a dependency between two transactions, then an apply process always applies the transaction with the lowest commit SCN first. The transaction with the higher commit SCN waits for the other transaction to commit. Therefore, if the apply server with the lowest commit SCN transaction is encountering contention, then the contention results from something other than a dependent transaction. In this case, you can monitor the apply server with the lowest commit SCN transaction to determine the cause of the contention.

The following four wait states are possible for an apply server:

- Not waiting: The apply server is not encountering contention and is not waiting. No action is necessary in this case.

- Waiting for an event that is not related to another session: An example of an event that is not related to another session is a `log` `file` `sync` event, where redo data must be flushed because of a commit or rollback. In these cases, nothing is written to the log initially because such waits are common and are usually transient. If the apply server is waiting for the same event after a certain interval of time, then the apply server writes a message to the alert log and apply process trace file. For example, an apply server `a001` might write a message similar to the following:

      A001: warning -- apply server 1, sid 26 waiting for event:
      A001: [log file sync] ...

  This output is written to the alert log at intervals until the problem is rectified.

- Waiting for an event that is related to a non apply server session: The apply server writes a message to the alert log and apply process trace file immediately. For example, an apply server `a001` might write a message similar to the following:

      A001: warning -- apply server 1, sid 10 waiting on user sid 36 for event:
      A001: [enq: TM - contention] name|mode=544d0003, object #=a078,
            table/partition=0

  This output is written to the alert log at intervals until the problem is rectified.

- Waiting for another apply server session: This state can be caused by interested transaction list (ITL) contention, but it can also be caused by more serious issues, such as an apply handler that obtains conflicting locks. In this case, the apply server that is blocked by another apply server prints only once to the alert log and the trace file for the apply process, and the blocked apply server issues a rollback to the blocking apply server. When the blocking apply server rolls back, another message indicating that the apply server has been rolled back is printed to the log files, and the rolled back transaction is reassigned by the coordinator process for the apply process.

  For example, if apply server 1 of apply process `a001` is blocked by apply server 2 of the same apply process (`a001`), then the apply process writes the following messages to the log files:

      A001: apply server 1 blocked on server 2
      A001: [enq: TX - row lock contention] name|mode=54580006, usn<<16 |
            slot=1000e, sequence=1853
      A001: apply server 2 rolled back

  You can determine the total number of times an apply server was rolled back since the apply process last started by querying the `TOTAL_ROLLBACKS` column in the `V$STREAMS_APPLY_COORDINATOR` dynamic performance view.

  See Also: Oracle Database Performance Tuning Guide for more information about contention and about resolving different types of contention Oracle Streams Concepts and Administration for more information about trace files and the alert log

### Is the Apply Process Waiting for a Dependent Transaction?

If you set the `parallelism` parameter for an apply process to a value greater than `1`, and you set the `commit_serialization` parameter of the apply process to `full`, then the apply process can detect interested transaction list (ITL) contention if there is a transaction that is dependent on another transaction with a higher SCN. ITL contention occurs if the session that created the transaction waited for an ITL slot in a block. This happens when the session wants to lock a row in the block, but one or more other sessions have rows locked in the same block, and there is no free ITL slot in the block.

ITL contention also is possible if the session is waiting due to a shared bitmap index fragment. Bitmap indexes index key values and a range of rowids. Each entry in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either `COMMIT` or `ROLLBACK`.

When an apply process detects such a dependency, it resolves the ITL contention automatically and records information about it in the alert log and apply process trace file for the database. ITL contention can negatively affect the performance of an apply process because there might not be any progress while it is detecting the deadlock.

To avoid the problem in the future, perform one of the following actions:

- Increase the number of ITLs available. You can do so by changing the `INITRANS` setting for the table using the `ALTER` `TABLE` statement.

- Set the `commit_serialization` parameter to `none` for the apply process.

- Set the `parallelism` apply process parameter to `1` for the apply process.

  See Also: Oracle Streams Concepts and Administration for more information about apply process parameters and about checking the trace files and alert log for problems Oracle Database Administrator's Guide and Oracle Database SQL Reference for more information about `INITRANS`

### Is an Apply Server Performing Poorly for Certain Transactions?

If an apply process is not performing well, then the reason might be that one or more apply servers used by the apply process are taking an inordinate amount of time to apply certain transactions. The following query displays information about the transactions being applied by each apply server used by an apply process named `strm01_apply`:

    COLUMN SERVER_ID HEADING 'Apply Server ID' FORMAT 99999999
    COLUMN STATE HEADING 'Apply Server State' FORMAT A20
    COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied Message|Number' FORMAT 99999999
    COLUMN MESSAGE_SEQUENCE HEADING 'Message Sequence|Number' FORMAT 99999999

    SELECT SERVER_ID, STATE, APPLIED_MESSAGE_NUMBER, MESSAGE_SEQUENCE
      FROM V$STREAMS_APPLY_SERVER
      WHERE APPLY_NAME = 'STRM01_APPLY'
      ORDER BY SERVER_ID;

If you run this query repeatedly, then over time the apply server state, applied message number, and message sequence number should continue to change for each apply server as it applies transactions. If these values do not change for one or more apply servers, then the apply server might not be performing well. In this case, you should make sure that, for each table to which the apply process applies changes, every key column has an index.

If you have many such tables, then you might need to determine the specific table and DML or DDL operation that is causing an apply server to perform poorly. To do so, run the following query when an apply server is taking an inordinately long time to apply a transaction. In this example, assume that the name of the apply process is `strm01_apply` and that apply server number two is performing poorly:

    COLUMN OPERATION HEADING 'Operation' FORMAT A20
    COLUMN OPTIONS HEADING 'Options' FORMAT A20
    COLUMN OBJECT_OWNER HEADING 'Object|Owner' FORMAT A10
    COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A10
    COLUMN COST HEADING 'Cost' FORMAT 99999999

    SELECT p.OPERATION, p.OPTIONS, p.OBJECT_OWNER, p.OBJECT_NAME, p.COST
      FROM V$SQL_PLAN p, V$SESSION s, V$STREAMS_APPLY_SERVER a
      WHERE a.APPLY_NAME = 'STRM01_APPLY' AND a.SERVER_ID = 2
        AND s.SID = a.SID
        AND p.HASH_VALUE = s.SQL_HASH_VALUE;

This query returns the operation being performed currently by the specified apply server. The query also returns the owner and name of the table on which the operation is being performed and the cost of the operation. Make sure each key column in this table has an index. If the results show `FULL` for the `COST` column, then the operation is causing full table scans, and indexing the table's key columns might solve the problem.

In addition, you can run the following query to determine the specific DML or DDL SQL statement that is causing an apply server to perform poorly, assuming that the name of the apply process is `strm01_apply` and that apply server number two is performing poorly:

    SELECT t.SQL_TEXT
      FROM V$SESSION s, V$SQLTEXT t, V$STREAMS_APPLY_SERVER a
      WHERE a.APPLY_NAME = 'STRM01_APPLY' AND a.SERVER_ID = 2
        AND s.SID = a.SID
        AND s.SQL_ADDRESS = t.ADDRESS
        AND s.SQL_HASH_VALUE = t.HASH_VALUE
        ORDER BY PIECE;

This query returns the SQL statement being run currently by the specified apply server. The statement includes the name of the table to which the transaction is being applied. Make sure each key column in this table has an index.

If the SQL statement returned by the previous query is less than one thousand characters long, then you can run the following simplified query instead:

    SELECT t.SQL_TEXT
      FROM V$SESSION s, V$SQLAREA t, V$STREAMS_APPLY_SERVER a
      WHERE a.APPLY_NAME = 'STRM01_APPLY' AND a.SERVER_ID = 2
        AND s.SID = a.SID
        AND s.SQL_ADDRESS = t.ADDRESS
        AND s.SQL_HASH_VALUE = t.HASH_VALUE;

See Also: Oracle Database Performance Tuning Guide and Oracle Database Reference for more information about the `V$SQL_PLAN` dynamic performance view

### Are There Any Apply Errors in the Error Queue?

When an apply process cannot apply a message, it moves the message and all of the other messages in the same transaction into the error queue. You should check for apply errors periodically to see if there are any transactions that could not be applied. You can check for apply errors by querying the `DBA_APPLY_ERROR` data dictionary view.

See Also: Oracle Streams Concepts and Administration for more information about checking for apply errors and about managing apply errors

#### Using a DML Handler to Correct Error Transactions

When an apply process moves a transaction to the error queue, you can examine the transaction to analyze the feasibility reexecuting the transaction successfully. If an abnormality is found in the transaction, then you might be able to configure a DML handler to correct the problem. In this case, configure the DML handler to run when you reexecute the error transaction.

When a DML handler is used to correct a problem in an error transaction, the apply process that uses the DML handler should be stopped to prevent the DML handler from acting on LCRs that are not involved with the error transaction. After successful reexecution, if the DML handler is no longer needed, then remove it. Also, correct the problem that caused the transaction to moved to the error queue to prevent future error transactions.

See Also: "Creating a DML Handler"

#### Troubleshooting Specific Apply Errors

You might encounter the following types of apply process errors for LCRs:

- ORA-01031 Insufficient Privileges

- ORA-01403 No Data Found

- ORA-23605 Invalid Value for Streams Parameter*

- ORA-23607 Invalid Column*

- ORA-24031 Invalid Value, parameter_name Should Be Non-NULL*

- ORA-26687 Instantiation SCN Not Set

- ORA-26688 Missing Key in LCR

- ORA-26689 Column Type Mismatch*

The errors marked with an asterisk (*) in the previous list often result from a problem with an apply handler or a rule-based transformation.

##### ORA-01031 Insufficient Privileges

An `ORA-01031` error occurs when the user designated as the apply user does not have the necessary privileges to perform SQL operations on the replicated objects. The apply user privileges must be granted by an explicit grant of each privilege. Granting these privileges through a role is not sufficient for the Streams apply user.

Specifically, the following privileges are required:

- For table level DML changes, the `INSERT`, `UPDATE`, `DELETE`, and `SELECT` privileges must be granted.

- For table level DDL changes, the `ALTER` `TABLE` privilege must be granted.

- For schema level changes, the `CREATE` `ANY` `TABLE`, `CREATE` `ANY` `INDEX`, `CREATE` `ANY` `PROCEDURE`, `ALTER` `ANY` `TABLE`, and `ALTER` `ANY` `PROCEDURE` privileges must be granted.

- For global level changes, `ALL` `PRIVILEGES` must be granted to the apply user.

To correct this error, complete the following steps:

1. Connect as the apply user on the destination database.

2. Query the `SESSION_PRIVS` data dictionary view to determine which required privileges are not granted to the apply user.

3. Connect as an administrative user who can grant privileges.

4. Grant the necessary privileges to the apply user.

5. Reexecute the error transactions in the error queue for the apply process.

See Also: "Apply and Streams Replication" for more information about apply users Oracle Streams Concepts and Administration for information about reexecuting error transactions

##### ORA-01403 No Data Found

Typically, an `ORA-01403` error occurs when an apply process tries to update an existing row and the `OLD_VALUES` in the row LCR do not match the current values at the destination database.

Typically, one of the following conditions causes this error:

- Supplemental logging is not specified for columns that require supplemental logging at the source database. In this case, LCRs from the source database might not contain values for key columns. You can use a DML handler to modify the LCR so that it contains the necessary supplemental data. See "Using a DML Handler to Correct Error Transactions". Also, specify the necessary supplemental logging at the source database to prevent future errors.

- There is a problem with the primary key in the table for which an LCR is applying a change. In this case, make sure the primary key is enabled by querying the `DBA_CONSTRAINTS` data dictionary view. If no primary key exists for the table, or if the target table has a different primary key than the source table, then specify substitute key columns using the `SET_KEY_COLUMNS` procedure in the `DBMS_APPLY_ADM` package. You also might encounter error `ORA-23416` if a table being applied does not have a primary key. After you make these changes, you can reexecute the error transaction.

- The transaction being applied depends on another transaction which has not yet executed. For example, if a transaction tries to update an employee with an `employee_id` of `300`, but the row for this employee has not yet been inserted into the `employees` table, then the update fails. In this case, execute the transaction on which the error transaction depends. Then, reexecute the error transaction.

- There is a data mismatch between a row LCR and the table for which the LCR is applying a change. Make sure row data in the table at the destination database matches the row data in the LCR. When you are checking for differences in the data, if there are any `DATE` columns in the shared table, then make sure your query shows the hours, minutes, and seconds. If there is a mismatch, then you can use a DML handler to modify an LCR so that it matches the table. See "Using a DML Handler to Correct Error Transactions".

  Alternatively, you can update the current values in the row so that the row LCR can be applied successfully. If changes to the row are captured by a capture process at the destination database, then you probably do not want to replicate this manual change to destination databases. In this case, complete the following steps:

  1. Set a tag in the session that corrects the row. Make sure you set the tag to a value that prevents the manual change from being replicated. For example, the tag can prevent the change from being captured by a capture process.

         EXEC DBMS_STREAMS.SET_TAG(tag => HEXTORAW('17'));

     In some environments, you might need to set the tag to a different value.

  2. Update the row in the table so that the data matches the old values in the LCR.

  3. Reexecute the error or reexecute all errors. To reexecute an error, run the `EXECUTE_ERROR` procedure in the `DBMS_APPLY_ADM` package, and specify the transaction identifier for the transaction that caused the error. For example:

         EXEC DBMS_APPLY_ADM.EXECUTE_ERROR(local_transaction_id => '5.4.312');

     Or, execute all errors for the apply process by running the `EXECUTE_ALL_ERRORS` procedure:

         EXEC DBMS_APPLY_ADM.EXECUTE_ALL_ERRORS(apply_name => 'APPLY');

  4. If you are going to make other changes in the current session that you want to replicate destination databases, then reset the tag for the session to an appropriate value, as in the following example:

         EXEC DBMS_STREAMS.SET_TAG(tag => NULL);

     In some environments, you might need to set the tag to a value other than `NULL`.

     See Also: "Supplemental Logging for Streams Replication" and "Monitoring Supplemental Logging" "Considerations for Applying DML Changes to Tables" for information about possible causes of apply errors Chapter 4, "Streams Tags" Oracle Streams Concepts and Administration for more information about managing apply errors and for instructions that enable you to display detailed information about apply errors

##### ORA-23605 Invalid Value for Streams Parameter

When calling row LCR (`SYS.LCR$_ROW_RECORD` type) member subprograms, an `ORA-23605` error might be raised if the values of the parameters passed by the member subprogram do not match the row LCR. For example, an error results if a member subprogram tries to add an old column value to an insert row LCR, or if a member subprogram tries to set the value of a LOB column to a number.

Row LCRs should contain the following old and new values, depending on the operation:

- A row LCR for an `INSERT` operation should contain new values but no old values.

- A row LCR for an `UPDATE` operation can contain both new values and old values.

- A row LCR for a `DELETE` operation should contain old values but no new values.

Verify that the correct parameter type (`OLD`, or `NEW`, or both) is specified for the row LCR operation (`INSERT`, `UPDATE`, or `DELETE`). For example, if a DML handler or custom rule-based transformation changes an `UPDATE` row LCR into an `INSERT` row LCR, then the handler or transformation should remove the old values in the row LCR.

If an apply handler caused the error, then correct the apply handler and reexecute the error transaction. If a custom rule-based transformation caused the error, then you might be able to create a DML handler to correct the problem. See "Using a DML Handler to Correct Error Transactions". Also, correct the rule-based transformation to avoid future errors.

See Also: Oracle Streams Concepts and Administration for more information about rule-based transformations

##### ORA-23607 Invalid Column

An `ORA-23607` error is raised by a row LCR (`SYS.LCR$_ROW_RECORD` type) member subprogram, when the value of the `column_name` parameter in the member subprogram does not match the name of any of the columns in the row LCR. Check the column names in the row LCR.

If an apply handler caused the error, then correct the apply handler and reexecute the error transaction. If a custom rule-based transformation caused the error, then you might be able to create a DML handler to correct the problem. See "Using a DML Handler to Correct Error Transactions". Also, correct the rule-based transformation to avoid future errors.

An apply handler or custom rule-based transformation can cause this error by using one of the following row LCR member procedures:

- `DELETE_COLUMN`, if this procedure tries to delete a column from a row LCR that does not exist in the row LCR

- `RENAME_COLUMN`, if this procedure tries to rename a column that does not exist in the row LCR

In this case, to avoid similar errors in the future, perform one of the following actions:

- Instead of using an apply handler or custom rule-based transformation to delete or rename a column in row LCRs, use a declarative rule-based transformation. If a declarative rule-based transformation tries to delete or rename a column that does not exist, then the declarative rule-based transformation does not raise an error. You can specify a declarative rule-based transformation that deletes a column using the `DBMS_STREAMS_ADM.DELETE_COLUMN` procedure, and you can specify a declarative rule-based transformation that renames a column using the `DBMS_STREAMS_ADM.RENAME_COLUMN` procedure. You can use a declarative rule-based transformation in combination with apply handlers and custom rule-based transformations.

- If you want to continue to use an apply handler or custom rule-based transformation to delete or rename a column in row LCRs, then modify the handler or transformation to prevent future errors. For example, modify the handler or transformation to verify that a column exists before trying to rename or delete the column.

See Also: Oracle Streams Concepts and Administration for more information about rule-based transformations Oracle Database PL/SQL Packages and Types Reference for more information about the `DELETE_COLUMN` and `RENAME_COLUMN` member procedures for row LCRs

##### ORA-24031 Invalid Value, parameter_name Should Be Non-NULL

An `ORA-24031` error can occur when an apply handler or a custom rule-based transformation passes a `NULL` value to an LCR member subprogram instead of an `ANYDATA` value that contains a `NULL`.

For example, the following call to the `ADD_COLUMN` member procedure for row LCRs can result in this error:

    new_lcr.ADD_COLUMN('OLD','LANGUAGE',NULL);

The following example shows the correct way to call the `ADD_COLUMN` member procedure for row LCRs:

    new_lcr.ADD_COLUMN('OLD','LANGUAGE',ANYDATA.ConvertVarchar2(NULL));

If an apply handler caused the error, then correct the apply handler and reexecute the error transaction. If a custom rule-based transformation caused the error, then you might be able to create a DML handler to correct the problem. See "Using a DML Handler to Correct Error Transactions". Also, correct the rule-based transformation to avoid future errors.

See Also: Oracle Streams Concepts and Administration for more information about rule-based transformations

##### ORA-26687 Instantiation SCN Not Set

Typically, an `ORA-26687` error occurs because the instantiation SCN is not set on an object for which an apply process is attempting to apply changes. You can query the `DBA_APPLY_INSTANTIATED_OBJECTS` data dictionary view to list the objects that have an instantiation SCN.

You can set an instantiation SCN for one or more objects by exporting the objects at the source database, and then importing them at the destination database. You can use either Data Pump export/import or original export/import. If you do not want to use export/import, then you can run one or more of the following procedures in the `DBMS_APPLY_ADM` package:

- `SET_TABLE_INSTANTIATION_SCN`

- `SET_SCHEMA_INSTANTIATION_SCN`

- `SET_GLOBAL_INSTANTIATION_SCN`

Some of the common reasons why an instantiation SCN is not set for an object at a destination database include the following:

- You used export/import for instantiation, and you exported the objects from the source database before preparing the objects for instantiation. You can prepare objects for instantiation either by creating Streams rules for the objects with the `DBMS_STREAMS_ADM` package or by running a procedure in the `DBMS_CAPTURE_ADM` package. If the objects were not prepared for instantiation before the export, then the instantiation SCN information will not be available in the export file, and the instantiation SCNs will not be set.

  In this case, prepare the database objects for instantiation at the source database by following the instructions in "Preparing Database Objects for Instantiation at a Source Database". Next, set the instantiation SCN for the database objects at the destination database.

- You used original export/import for instantiation, and you performed the import without specifying `y` for the `STREAMS_INSTANTIATION` import parameter. If this parameter is not set to `y` for the import, then the instantiation SCN will not be set.

  In this case, repeat the original export/import operation, and set the `STREAMS_INSTANTIATION` parameter to `y` during import. Follow the instructions in "Instantiating Objects in a Tablespace Using Transportable Tablespace or RMAN".

  Alternatively, use Data Pump export/import. An instantiation SCN is set for each imported prepared object automatically when you use Data Pump import.

- Instead of using export/import for instantiation, you set the instantiation SCN explicitly with the appropriate procedure in the `DBMS_APPLY_ADM` package. When the instantiation SCN is set explicitly by the database administrator, responsibility for the correctness of the data is assumed by the administrator.

  In this case, set the instantiation SCN for the database objects explicitly by following the instructions in "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package". Alternatively, you can choose to perform a metadata-only export/import to set the instantiation SCNs by following the instructions in "Setting Instantiation SCNs at a Destination Database".

- You want to apply DDL changes, but you did not set the instantiation SCN at the schema or global level.

  In this case, set the instantiation SCN for the appropriate schemas by running the `SET_SCHEMA_INSTANTIATION_SCN` procedure, or set the instantiation SCN for the source database by running the `SET_GLOBAL_INSTANTIATION_SCN` procedure. Both of these procedures are in the `DBMS_APPLY_ADM` package. Follow the instructions in "Setting Instantiation SCNs Using the DBMS_APPLY_ADM Package".

After you correct the condition that caused the error, whether you should reexecute the error transaction or delete it depends on whether the changes included in the transaction were executed at the destination database when you corrected the error condition. Follow these guidelines when you decide whether you should reexecute the transaction in the error queue or delete it:

- If you performed a new export/import, and the new export includes the transaction in the error queue, then delete the transaction in the error queue.

- If you set instantiation SCNs explicitly or reimported an existing export dump file, then reexecute the transaction in the error queue.

  See Also: "Overview of Instantiation and Streams Replication" "Setting Instantiation SCNs at a Destination Database" Oracle Streams Concepts and Administration for information about reexecuting and deleting error transactions

##### ORA-26688 Missing Key in LCR

Typically, an `ORA-26688` error occurs because of one of the following conditions:

- At least one LCR in a transaction does not contain enough information for the apply process to apply it. For dependency computation, an apply process always needs values for the defined primary key column(s) at the destination database. Also, if the parallelism of any apply process that will apply the changes is greater than 1, then the apply process needs values for any indexed column at a destination database, which includes unique or non unique index columns, foreign key columns, and bitmap index columns.

  If an apply process needs values for a column, and the column exists at the source database, then this error results when supplemental logging is not specified for one or more of these columns at the source database. In this case, specify the necessary supplemental logging at the source database to prevent apply errors.

  However, the definition of the source database table might be different than the definition of the corresponding destination database table. If an apply process needs values for a column, and the column exists at the destination database but does not exist at the source database, then you can configure a rule-based transformation to add the required values to the LCRs from the source database to prevent apply errors.

  To correct a transaction placed in the error queue because of this error, you can use a DML handler to modify the LCRs so that they contain the necessary supplemental data. See "Using a DML Handler to Correct Error Transactions".

- There is a problem with the primary key in the table for which an LCR is applying a change. In this case, make sure the primary key is enabled by querying the `DBA_CONSTRAINTS` data dictionary view. If no primary key exists for the table, or if the destination table has a different primary key than the source table, then specify substitute key columns using the `SET_KEY_COLUMNS` procedure in the `DBMS_APPLY_ADM` package. You can also encounter error `ORA-23416` if a table does not have a primary key. After you make these changes, you can reexecute the error transaction.

  See Also: "Supplemental Logging for Streams Replication" "Substitute Key Columns" Oracle Streams Concepts and Administration for more information about rule-based transformations

##### ORA-26689 Column Type Mismatch

Typically, an `ORA-26689` error occurs because one or more columns at a table in the source database do not match the corresponding columns at the destination database. The LCRs from the source database might contain more columns than the table at the destination database, or there might be a column name or column type mismatch for one or more columns. If the columns differ at the databases, then you can use rule-based transformations to avoid future errors.

If you use an apply handler or a custom rule-based transformation, then make sure any `ANYDATA` conversion functions match the datatype in the LCR that is being converted. For example, if the column is specified as `VARCHAR2`, then use `ANYDATA.CONVERTVARCHAR2` function to convert the data from type `ANY` to `VARCHAR2`.

Also, make sure you use the correct character case in rule conditions, apply handlers, and rule-based transformations. For example, if a column name has all uppercase characters in the data dictionary, then you should specify the column name with all uppercase characters in rule conditions, apply handlers, and rule-based transformations

This error can also occur because supplemental logging is not specified where it is required for nonkey columns at the source database. In this case, LCRs from the source database might not contain needed values for these nonkey columns.

You might be able to configure a DML handler to apply the error transaction. See "Using a DML Handler to Correct Error Transactions".

See Also: "Considerations for Applying DML Changes to Tables" for information about possible causes of apply errors "Supplemental Logging for Streams Replication" and "Monitoring Supplemental Logging" Oracle Streams Replication Administrator's Guide for information about rule-based transformations